package edu.upc.galaxy.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.*;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.stereotype.Repository;

/**
 *
 * @author gian
 */
@Repository
public class BaseDao implements Serializable {
    
    private String databaseName = "jdbc:hsqldb:file:bd/galaxy;shutdown=true";
    private String username = "sa";
    private String password = "";   
    private Connection connection = null;
    private PreparedStatement pstmt;
    
    private final static Logger logger = Logger.getLogger(BaseDao.class .getName());
    
    public BaseDao() {
        try {
            logger.setLevel(Level.ALL);
            logger.log(Level.INFO,"Cargando el driver HSQLDB");
            Class.forName("org.hsqldb.jdbcDriver");
            logger.log(Level.INFO,"Creando la conexion a la BD");
            connection = DriverManager.getConnection(databaseName,username,password); 
        } catch(Exception e) {
            logger.log(Level.SEVERE,e.getMessage());
        }
    }
    
    public Integer insert(String insert,List<Object> datos) {
        try {           
            pstmt = getConnection().prepareStatement(insert,Statement.RETURN_GENERATED_KEYS);
            int posicion = 1;
            
            for (Object dato:datos) {
                if (dato instanceof Integer) {                    
                    pstmt.setInt(posicion,(Integer)dato);
                    posicion++;
                }
                if (dato instanceof Double) {                    
                    pstmt.setDouble(posicion,(Double)dato);
                    posicion++;
                }
                if (dato instanceof Date) {
                    pstmt.setDate(posicion,(Date)dato);
                    posicion++;
                }
                if (dato instanceof BigDecimal) {
                    pstmt.setBigDecimal(posicion,(BigDecimal)dato);
                    posicion++;
                }
                if (dato instanceof String) {
                    pstmt.setString(posicion,(String)dato);
                    posicion++;
                }
                if (dato instanceof Short) {
                    pstmt.setShort(posicion,(Short)dato);
                    posicion++;
                }
                if (dato instanceof Boolean) {
                    pstmt.setBoolean(posicion,(Boolean)dato);
                    posicion++;
                }
            }
            pstmt.executeUpdate();
            ResultSet keys = pstmt.getGeneratedKeys();
            keys.next();
            Integer llave = keys.getInt(1);            
            return llave;
            
        } catch (Exception ex) {
            logger.log(Level.SEVERE,"Error insertando datos",ex);
        } finally {
            try {pstmt.execute("SHUTDOWN;");pstmt.close();} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {}                                     
        }
        return null;
    }
    
    public void update(String update,List datos) {
        try {            
            pstmt = connection.prepareStatement(update);
            int posicion = 1;
            
            for (Object dato:datos) {
                if (dato instanceof Integer) {                    
                    pstmt.setInt(posicion,(Integer)dato);
                    posicion++;
                }
                if (dato instanceof Double) {                    
                    pstmt.setDouble(posicion,(Double)dato);
                    posicion++;
                }
                if (dato instanceof Date) {
                    pstmt.setDate(posicion,(Date)dato);
                    posicion++;
                }
                if (dato instanceof BigDecimal) {
                    pstmt.setBigDecimal(posicion,(BigDecimal)dato);
                    posicion++;
                }
                if (dato instanceof String) {
                    pstmt.setString(posicion,(String)dato);
                    posicion++;
                }
                if (dato instanceof Short) {
                    pstmt.setShort(posicion,(Short)dato);
                    posicion++;
                }    
                if (dato instanceof Boolean) {
                    pstmt.setBoolean(posicion,(Boolean)dato);
                    posicion++;
                }                
            }
            pstmt.executeUpdate();
        } catch (Exception ex) {
            logger.log(Level.SEVERE,"Error actualizando datos",ex);
        } finally {
            try {pstmt.execute("SHUTDOWN;");pstmt.close();} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {}
        }    
    }
    
    public void delete(String delete,Integer id) {
        try {            
            pstmt = connection.prepareStatement(delete);
            pstmt.setInt(1,id);
            pstmt.execute();
        } catch (Exception ex) {
            logger.log(Level.SEVERE, "Error borrando el dato {0}", id);
        } finally {
            try {pstmt.execute("SHUTDOWN;");pstmt.close();} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {}
        }
    }
    
    public ResultSet select(String select) {      
        try {            
            pstmt = connection.prepareStatement(select);
            return pstmt.executeQuery();
        } catch (Exception ex) {           
            try {pstmt.execute("SHUTDOWN;");pstmt.close();} catch (Exception ex1) {}
            try {connection.close();} catch (Exception ex1) {}            
        }
        return null;
    }
    
    public ResultSet select(String select,Integer rowLimit) {
        try {            
            pstmt = connection.prepareStatement(select);
            pstmt.setMaxRows(rowLimit);            
            return pstmt.executeQuery();
        } catch (Exception ex) {          
            try {pstmt.execute("SHUTDOWN;");pstmt.close();} catch (Exception ex1) {}
            try {connection.close();} catch (Exception ex1) {}                        
        }
        return null;
    }    

    public Connection getConnection() {
        return connection;
    }      
}
